package com.xyh.builder;

import com.xyh.bean.Constants;
import com.xyh.bean.FieldInfo;
import com.xyh.bean.TableInfo;
import com.xyh.config.DatabaseConfig;
import com.xyh.utils.JsonUtils;
import com.xyh.utils.PropertiesUtils;
import com.xyh.utils.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BuilderTable {
    private static final Logger logger = LoggerFactory.getLogger(BuilderTable.class);
    private static final String SQL_SHOW_TABLE_STATUS = "SHOW TABLE STATUS";
    private static final String SQL_SHOW_TABLE_FIELDS = "SHOW FULL FIELDS FROM %s";
    private static final String SQL_SHOW_TABLE_INDEX = "SHOW INDEX FROM %s";

    public static List<TableInfo> getTables() {
        List<TableInfo> tableInfoList = new ArrayList<>();
        
        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement ps = conn.prepareStatement(SQL_SHOW_TABLE_STATUS);
             ResultSet tableResult = ps.executeQuery()) {
            
            while (tableResult.next()) {
                String tableName = tableResult.getString("Name");
                String comment = tableResult.getString("Comment");

                TableInfo tableInfo = new TableInfo();
                String beanName = processTableName(tableName);
                
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_PARAM);

                getFieldInfo(conn, tableInfo);
                getKeyIndexInfo(conn, tableInfo);

                tableInfoList.add(tableInfo);
            }

            logger.info("成功获取{}个表信息", tableInfoList.size());
            if (logger.isDebugEnabled()) {
                logger.debug("tableInfo:{}", JsonUtils.convertObj2Json(tableInfoList));
            }
        } catch (SQLException e) {
            logger.error("读取数据库表信息失败", e);
            throw new RuntimeException("读取数据库表信息失败", e);
        }
        
        return tableInfoList;
    }

    private static String processTableName(String tableName) {
        if (StringUtils.isEmpty(tableName)) {
            throw new IllegalArgumentException("表名不能为空");
        }

        String beanName = tableName;
        if (Constants.IGNORE_TABLE_PREFIX) {
            int underscoreIndex = tableName.indexOf("_");
            if (underscoreIndex > 0) {
                beanName = tableName.substring(underscoreIndex + 1);
            }
        }

        beanName = processField(beanName, true);
        return ignoreTableSuffix(beanName);
    }

    private static void getFieldInfo(Connection conn, TableInfo tableInfo) throws SQLException {
        List<FieldInfo> fieldInfoList = new ArrayList<>();
        List<FieldInfo> fieldExtendList = new ArrayList<>();
        
        String sql = String.format(SQL_SHOW_TABLE_FIELDS, tableInfo.getTableName());
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet fieldResult = ps.executeQuery()) {

            while (fieldResult.next()) {
                FieldInfo fieldInfo = processFieldResult(fieldResult);
                fieldInfoList.add(fieldInfo);
                
                addExtendedFields(fieldInfo, fieldExtendList);
            }
            
            tableInfo.setExtendFieldList(fieldExtendList);
            updateTableStatus(tableInfo, fieldInfoList);
            tableInfo.setFieldList(fieldInfoList);

        } catch (SQLException e) {
            logger.error("读取表字段失败: 表名={}, SQL={}", 
                    tableInfo.getTableName(),
                    sql,
                    e);
            throw e;
        }
    }

    private static FieldInfo processFieldResult(ResultSet fieldResult) throws SQLException {
        String field = fieldResult.getString("Field");
        String type = fieldResult.getString("Type");
        String comment = fieldResult.getString("Comment");
        String extra = fieldResult.getString("Extra");

        // 处理类型（如varchar(255) -> varchar）
        int parenthesesIndex = type.indexOf('(');
        if (parenthesesIndex > 0) {
            type = type.substring(0, parenthesesIndex);
        }

        FieldInfo fieldInfo = new FieldInfo();
        fieldInfo.setFieldName(field);
        fieldInfo.setSqlType(type);
        fieldInfo.setComment(comment);
        fieldInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(extra));
        fieldInfo.setPropertyName(processField(field, false));
        fieldInfo.setJavaType(processJavaType(type));

        return fieldInfo;
    }

    private static void addExtendedFields(FieldInfo fieldInfo, List<FieldInfo> fieldExtendList) {
        // 为字符串类型添加模糊查询字段
        if (Constants.SQL_STRING_TYPES.contains(fieldInfo.getSqlType())) {
            FieldInfo fuzzField = createExtendedField(fieldInfo, Constants.SUFFIX_BEAN_PARAM_FUZZY);
            fieldExtendList.add(fuzzField);
        }
        
        // 为日期类型添加开始和结束时间字段
        if (Constants.SQL_DATE_TYPES.contains(fieldInfo.getSqlType()) || 
            Constants.SQL_DATE_TIMES_TYPES.contains(fieldInfo.getSqlType())) {
            fieldExtendList.add(createExtendedField(fieldInfo, Constants.SUFFIX_BEAN_PARAM_TIME_START));
            fieldExtendList.add(createExtendedField(fieldInfo, Constants.SUFFIX_BEAN_PARAM_TIME_END));
        }
    }

    private static FieldInfo createExtendedField(FieldInfo originalField, String suffix) {
        FieldInfo extendedField = new FieldInfo();
        extendedField.setJavaType("String");
        extendedField.setSqlType(originalField.getSqlType());
        extendedField.setPropertyName(originalField.getPropertyName() + suffix);
        extendedField.setFieldName(originalField.getFieldName());
        return extendedField;
    }

    private static void updateTableStatus(TableInfo tableInfo, List<FieldInfo> fieldInfoList) {
        tableInfo.setHaveDateTime(fieldInfoList.stream()
                .anyMatch(f -> Constants.SQL_DATE_TIMES_TYPES.contains(f.getSqlType())));
        
        tableInfo.setHaveDate(fieldInfoList.stream()
                .anyMatch(f -> Constants.SQL_DATE_TYPES.contains(f.getSqlType())));
        
        tableInfo.setHaveBigDecimal(fieldInfoList.stream()
                .anyMatch(f -> Constants.SQL_DECIMAL_TYPES.contains(f.getSqlType())));
    }

    private static void getKeyIndexInfo(Connection conn, TableInfo tableInfo) throws SQLException {
        String sql = String.format(SQL_SHOW_TABLE_INDEX, tableInfo.getTableName());
        
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet fieldResult = ps.executeQuery()) {

            // 预处理字段列表为Map（提升查找性能）
            Map<String, FieldInfo> fieldMap = new HashMap<>();
            List<FieldInfo> fieldList = tableInfo.getFieldList();
            if (fieldList != null) {
                for (FieldInfo field : fieldList) {
                    fieldMap.put(field.getFieldName().toLowerCase(), field);
                }
            }

            // 遍历索引结果集
            while (fieldResult.next()) {
                String keyName = fieldResult.getString("Key_name");
                int nonUnique = fieldResult.getInt("Non_unique");
                String columnName = fieldResult.getString("Column_name");

                // 跳过非唯一索引
                if (nonUnique == 1) {
                    continue;
                }

                // 判断字段名是否以 'id' 结尾
                if (columnName.toLowerCase().endsWith("id")) {
                    // 通过Map直接获取字段（O(1)复杂度）
                    FieldInfo field = fieldMap.get(columnName.toLowerCase());
                    if (field != null) {
                        tableInfo.getKeyIndexMap()
                                .computeIfAbsent(keyName, k -> new ArrayList<>())
                                .add(field);
                    } else {
                        logger.warn("索引字段未找到: 表名={}, 索引名={}, 字段名={}",
                                tableInfo.getTableName(), keyName, columnName);
                    }
                }
            }
        } catch (SQLException e) {
            logger.error("读取索引失败: 表名={}, SQL={}", tableInfo.getTableName(), sql, e);
            throw e;
        }
    }

    public static String ignoreTableSuffix(String beanName) {
        if (StringUtils.isEmpty(Constants.IGNORE_TABLE_SUFFIX) || StringUtils.isEmpty(beanName)) {
            return beanName;
        }

        String[] suffixes = Constants.IGNORE_TABLE_SUFFIX.split(",");
        List<String> suffixList = new ArrayList<>();
        for (String s : suffixes) {
            String trimmedSuffix = s.trim();
            if (!trimmedSuffix.isEmpty()) {
                suffixList.add(trimmedSuffix);
            }
        }

        suffixList.sort((a, b) -> Integer.compare(b.length(), a.length()));

        for (String suffix : suffixList) {
            if (beanName.endsWith(suffix)) {
                return beanName.substring(0, beanName.length() - suffix.length());
            }
        }

        return beanName;
    }

    private static String processJavaType(String type) {
        if (Constants.SQL_STRING_TYPES.contains(type)) {
            return "String";
        } else if (Constants.SQL_INT_TYPES.contains(type)) {
            return "Integer";
        } else if (Constants.SQL_LONG_TYPES.contains(type)) {
            return "Long";
        } else if (Constants.SQL_DECIMAL_TYPES.contains(type)) {
            return "BigDecimal";
        } else if (Constants.SQL_DATE_TYPES.contains(type) || Constants.SQL_DATE_TIMES_TYPES.contains(type)) {
            return "Date";
        } else {
            logger.warn("不支持的数据类型：{}，已自动转换为Object类型", type);
            return "Object";
        }
    }

    private static String processField(String field, Boolean upperCaseFirstLetter) {
        if (StringUtils.isEmpty(field)) {
            return field;
        }
        
        StringBuffer sf = new StringBuffer();
        String[] fields = field.split("_");
        sf.append(upperCaseFirstLetter ? StringUtils.upperCaseFirstLetter(fields[0]) : fields[0]);
        for (int i = 1; i < fields.length; i++) {
            sf.append(StringUtils.upperCaseFirstLetter(fields[i]));
        }
        return sf.toString();
    }
}

